﻿' ------------------------------------------------------------------------------
' This class contains methods for managing the tables in the database. It
' checks, deletes, and creates the tables used by the application. It can
' load the Album table with a minimal set of predefined albums, and load the
' spatial tables (TimeZone, City, County, and State) with spatial data.
'
' Generally speaking, the tables are created by loading the DDL text from sql
' script files located in the SpatialCore.Resources assembly, altering the
' DDL using String.Format(), and then executing the code via a SqlCommand
' object.
'
' The Photo table's definition bears some elaboration. There are two sets of
' columns in this table: those defined in advance by the sql scripts, and those
' that correspond to metadata elements. The metadata columns are defined at
' runtime based on the MetaSpec collection in a MetaMapper object. These are
' file-based (though fixed in the Resources assembly) and intended to be expanded
' in the next phase of development when more metadata elements are captured by
' the application.
'
' The LoadXxxxTable methods appear to be highly redundant, but there is enough
' detailed and different code for each table to have made refactoring a losing
' proposition.
'
' When recreating the Album table, the table's contents can be saved first and
' reloaded after the table has been created anew.
'
' As mentioned, the sql DDL for creating the tables are contained in the 
' SpatialAlbum.Resources assembly. These scripts can be enhanced to implement
' better indexing and storage specifications if needed.
'
' The data for the spatial tables are also contained in that assembly. The 
' spatial data is contained in semicolon-delimited text files containing
' well-known text representations of the spatial features.
' ------------------------------------------------------------------------------
' This file is part of the SQL Server 2008 Technology Demonstration. This
' application was designed and coded by Eric Weitzman, LandTime Inc, for
'' Microsoft Corporation. Contact Ed Katibah at Microsoft about this demo.
' Copyright © 2008 Microsoft Corporation. All rights reserved.
' ------------------------------------------------------------------------------

Public Class Database


    Private Shared m_tablenames As ArrayList = Nothing
    Private Shared m_photocolumnnames As ArrayList = Nothing

    Public Shared Function TableNames() As ArrayList
        If m_tablenames IsNot Nothing Then Return m_tablenames

        m_tablenames = New ArrayList
        For Each table As String In New String() { _
            Schema.Photo, Schema.Album, Schema.TimeZone, _
            Schema.City, Schema.County, Schema.State, Schema.Metro}
            m_tablenames.Add(table)
        Next
        Return m_tablenames
    End Function

    Public Function ColumnNames(ByVal table As String, ByVal connection As Connection) As ArrayList
        If m_photocolumnnames IsNot Nothing Then Return m_photocolumnnames

        m_photocolumnnames = New ArrayList
        Dim rdr As SqlDataReader = Nothing
        Try
            Dim cmd As SqlCommand = connection.CreateCommand
            cmd.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '" & table & "'"
            ReportSqlCommand("get column names: " & table, cmd)
            rdr = cmd.ExecuteReader()
            While rdr.Read()
                m_photocolumnnames.Add(rdr.Item(0))
            End While
        Catch ex As Exception
            ReportException(ex)
        Finally
            If rdr IsNot Nothing Then rdr.Close()
        End Try

        Return m_photocolumnnames
    End Function

    Public Sub CreateDatabase(ByVal overwrite As Boolean, ByVal keepalbums As Boolean, ByVal connection As Connection)

        Dim albums As DataTable = Nothing
        If keepalbums Then albums = GetDataTable(Schema.Album, connection)

        CreatePhotoTable(overwrite, connection)
        CreateTableCheck(Schema.Album, SpatialAlbum.Resources.My.Resources.CreateAlbumTable, overwrite, connection)
        CreateTableCheck(Schema.TimeZone, SpatialAlbum.Resources.My.Resources.CreateTimeZoneTable, overwrite, connection)
        CreateTableCheck(Schema.City, SpatialAlbum.Resources.My.Resources.CreateCityTable, overwrite, connection)
        CreateTableCheck(Schema.County, SpatialAlbum.Resources.My.Resources.CreateCountyTable, overwrite, connection)
        CreateTableCheck(Schema.State, SpatialAlbum.Resources.My.Resources.CreateStateTable, overwrite, connection)
        CreateTableCheck(Schema.Metro, SpatialAlbum.Resources.My.Resources.CreateMetroTable, overwrite, connection)

        If albums Is Nothing Then
            LoadAlbumTable(connection)
        Else
            Dim count As Integer = LoadFromDataTable(Schema.Album, albums, connection)
            If count = 0 Then LoadAlbumTable(connection)
        End If

        LoadTimeZoneTable(connection)
        LoadCityTable(connection)
        LoadCountyTable(connection)
        LoadStateTable(connection)
        LoadMetroTable(connection)
    End Sub

    Public Function ClearPhotoTable(ByVal connection As Connection) As Boolean
        Dim table As String = Schema.Photo
        Try
            Dim cmd As SqlCommand = connection.CreateCommand
            cmd.CommandText = "DELETE FROM " & table
            ReportSqlCommand("clear Photo table", cmd)
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            ReportException(ex)
            Return False
        End Try
    End Function

    Public Sub CreatePhotoTable(ByVal overwrite As Boolean, ByVal connection As Connection)

        Dim table As String = Schema.Photo

        Dim mm As New MetaMapper(SpatialAlbum.Resources.My.Resources.GpsExifMappings)
        Dim sbmetacolumns As New System.Text.StringBuilder

        For Each spec As MetaSpec In mm.MetaSpecs.Values
            If spec.ColumnName <> "" Then _
                sbmetacolumns.Append(spec.ColumnName).Append(" ").Append(spec.ColumnSql).AppendLine(","c)
        Next
        Dim sql As String = String.Format(SpatialAlbum.Resources.My.Resources.CreatePhotoTable, table, sbmetacolumns.ToString)

        If Not OkayToCreate(table, overwrite, connection) Then Return
        If overwrite Then DropTable(table, connection)
        CreateTable(table, sql, connection)
    End Sub

    Public Sub CreateTableCheck(ByVal table As String, ByVal sql As String, ByVal overwrite As Boolean, ByVal connection As Connection)
        If Not OkayToCreate(table, overwrite, connection) Then Return
        If overwrite Then DropTable(table, connection)
        CreateTable(table, String.Format(sql, table), connection)
    End Sub


    Public Function CheckDatabase(ByVal connection As Connection) As Boolean
        Dim result As Boolean = True
        Dim check As Boolean

        Dim stats As DatabaseStatistics = DatabaseStatistics.GetStatistics(connection)

        For Each table As String In TableNames()
            check = (stats.Count(table) <> -1)
            If check Then ReportTrace(table & " table okay")
            If Not check Then result = False
        Next

        Return result
    End Function

    Public Function LoadFromDataTable(ByVal table As String, ByVal dt As DataTable, ByVal connection As Connection) As Integer
        If dt Is Nothing OrElse dt.Rows.Count = 0 Then Return 0

        Dim nrec As Integer = 0
        Try
            Dim cmd As SqlCommand = connection.CreateCommand
            Dim sb As New System.Text.StringBuilder
            sb.Append("INSERT INTO ").Append(table).Append(" VALUES (")
            For Each col As DataColumn In dt.Columns
                Dim par As New SqlParameter(col.ColumnName, col.DataType)
                cmd.Parameters.Add(par)
                sb.Append("@").Append(col.ColumnName).Append(", ")
            Next
            sb.Remove(sb.Length - 2, 2)
            sb.Append(")")
            cmd.CommandText = sb.ToString

            For Each row As DataRow In dt.Rows
                For i As Integer = 0 To dt.Columns.Count - 1
                    cmd.Parameters.Item(i).Value = row.Item(i)
                Next
                ReportSqlCommand("insert row", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Next
        Catch ex As Exception
            ReportException(ex)
            Return 0
        End Try

        Return nrec
    End Function

    Private Function DatafilePath(ByVal name As String, ByVal connection As Connection) As String
        If connection.CoordinateOrder <> EnumCoordinateOrder.eLatitudeLongitudeObsolete Then
            Return IO.Path.Combine(IO.Directory.GetCurrentDirectory(), name)
        Else
            Return IO.Path.Combine(IO.Directory.GetCurrentDirectory(), name.Replace(".txt", "Obsolete.txt"))
        End If
    End Function

    Public Sub LoadMetroTable(ByVal connection As Connection)

        Const loginterval As Integer = 500
        Dim table As String = Schema.Metro
        Dim datafile As String = DatafilePath(RuntimeSupport.MetroData, connection)

        ReportTrace("loading table: " & table & " file: " & datafile)
        ReportTrace("NOTE: sql will be shown for every " & loginterval & "th record inserted")

        Dim cmd As SqlCommand = connection.CreateCommand()

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 5 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                Dim guid As System.Guid = System.Guid.NewGuid()
                Dim metro As String = a(2).ToString.Trim
                If metro.Contains("'"c) Then metro = metro.Replace("'", "''")
                cmd.CommandText = String.Format("INSERT INTO " & table & " VALUES ({0}, '{1}', '{2}', '{3}', '{4}')", a(0).Trim, a(1).Trim, metro, a(3).Trim, a(4).Trim)
                If (CInt(nrec / loginterval) * loginterval) = nrec Then ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))
    End Sub

    Public Sub LoadCityTable(ByVal connection As Connection)

        Const loginterval As Integer = 500
        Dim table As String = Schema.City
        Dim datafile As String = DatafilePath(RuntimeSupport.CityData, connection)

        ReportTrace("loading table: " & table & " file: " & datafile)
        ReportTrace("NOTE: sql will be shown for every " & loginterval & "th record inserted")

        Dim cmd As SqlCommand = connection.CreateCommand()

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 6 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                Dim guid As System.Guid = System.Guid.NewGuid()
                Dim city As String = a(2).ToString.Trim
                If city.Contains("'"c) Then city = city.Replace("'", "''")
                cmd.CommandText = String.Format("INSERT INTO " & table & " VALUES ({0}, '{1}', '{2}', '{3}', '{4}', '{5}')", a(0).Trim, a(1).Trim, city, a(3).Trim, a(4).Trim, a(5).Trim)
                If (CInt(nrec / loginterval) * loginterval) = nrec Then ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))
    End Sub

    Public Sub LoadCountyTable(ByVal connection As Connection)

        Const loginterval As Integer = 500
        Dim table As String = Schema.County
        Dim datafile As String = DatafilePath(RuntimeSupport.CountyData, connection)

        ReportTrace("loading table: " & table & " file: " & datafile)
        ReportTrace("NOTE: sql will be shown for every " & loginterval & "th record inserted")

        Dim cmd As SqlCommand = connection.CreateCommand()

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 6 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                Dim guid As System.Guid = System.Guid.NewGuid()
                Dim county As String = a(1).ToString.Trim
                If county.Contains("'"c) Then county = county.Replace("'", "''")
                cmd.CommandText = String.Format("INSERT INTO " & table & " VALUES ({0}, '{1}', '{2}', {3}, {4}, '{5}')", a(0).Trim, county, a(2).Trim, a(3).Trim, a(4).Trim, a(5).Trim)
                If (CInt(nrec / loginterval) * loginterval) = nrec Then ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))
    End Sub

    Public Sub LoadStateTable(ByVal connection As Connection)

        Dim table As String = Schema.State
        Dim datafile As String = DatafilePath(RuntimeSupport.StateData, connection)

        ReportTrace("loading table: " & table & " file: " & datafile)

        Dim cmd As SqlCommand = connection.CreateCommand()

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 6 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                Dim guid As System.Guid = System.Guid.NewGuid()
                cmd.CommandText = String.Format("INSERT INTO " & table & " VALUES ({0}, '{1}', {2}, '{3}', '{4}', '{5}')", a(0).Trim, a(1).Trim, a(2).Trim, a(3).Trim, a(4).Trim, a(5).Trim)
                ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))
    End Sub

    Public Sub LoadAlbumTable(ByVal connection As Connection)

        Dim table As String = Schema.Album
        Dim datafile As String = DatafilePath(RuntimeSupport.AlbumData, connection)

        ReportTrace("loading table: " & table & " file: " & datafile)

        Dim cmd As SqlCommand = connection.CreateCommand()
        cmd.Parameters.Add("@SQL", SqlDbType.NVarChar)

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 2 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                Dim guid As System.Guid = System.Guid.NewGuid()
                cmd.CommandText = String.Format("INSERT INTO " & table & " VALUES ('{0}', '{1}', @SQL)", guid, a(0).Trim)
                cmd.Parameters.Item("@SQL").Value = a(1).Trim
                ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))
    End Sub

    Public Sub LoadTimeZoneTable(ByVal connection As Connection)

        Dim table As String = Schema.TimeZone
        Dim datafile As String = IO.Path.Combine(IO.Directory.GetCurrentDirectory(), RuntimeSupport.TimeZoneData)
        ReportTrace("loading table: " & table & " file: " & datafile)

        Dim cmd As SqlCommand = connection.CreateCommand()

        Dim sr As New System.IO.StreamReader(datafile)
        Dim s As String = sr.ReadLine
        Dim nrec As Integer = 1

        While Not s Is Nothing
            Dim a() As String = s.Split(New Char() {";"c})
            If a.Length <> 7 Then AssertFail("invalid text at line: " & nrec & " file: " & datafile)
            Try
                cmd.CommandText = String.Format("INSERT INTO {0} VALUES ('{1}', '{2}', '{3}', {4}, {5}, {6}, {7})", table, a(1).Trim, a(2).Trim, a(3).Trim, a(4).Trim, a(5).Trim, a(0).Trim, a(6).Trim)
                ReportSqlCommand("insert", cmd)
                cmd.ExecuteNonQuery()
                nrec += 1
            Catch ex As SqlException
                ReportError("record " & nrec & ": " & ex.Message)
            End Try
            s = sr.ReadLine
        End While
        ReportTrace(String.Format("inserted {0} record(s) into table {1}", nrec, table))

        cmd.CommandText = String.Format("UPDATE {0} SET ZonePolygon = ZonePolygon.MakeValid() FROM {0}", table)
        ReportSqlCommand("update TimeZone polygons", cmd)
        cmd.ExecuteNonQuery()
        ReportTrace(String.Format("ran MakeValid() on all polygons in {0}", table))

    End Sub

End Class
